package edu.yuhf.jdbcpart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import edu.yuhf.dbutil.DBConnection;
import edu.yuhf.dbutil.JdbcTemplate;
import edu.yuhf.domain.User;

public class UserTableOperationForJdbcTemplate {
	//查询数据库users表中所有的内容
		public List<User> queryAll(){
			String sql="select * from users";
			List<User> list=JdbcTemplate.query(sql, (rs)->{
				List<User> list0=new ArrayList<>();
				try {
					while(rs.next()) {
						User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
						list0.add(user);
					}
				}catch(SQLException e) {
					e.printStackTrace();
				}
				return list0;
			}, new Object[] {});
			return list;
		}
		//----------------------------------隔离区-----------隔离区-----------隔离区-----------------------------------------------
		//============================================以下下为测试用代码===========================================================
		
		//根据id进行查询  用JdbcTemplate接口
		public User querByIdX(int id) {
			String sql="select * from users where id=?";
			User user=JdbcTemplate.query(sql, (rs)->{
				User userx=null;
				try {
					while(rs.next()) {
					userx=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return userx;
			}, id);
			
			
			return user;
		}
		

		
		//----------------------------------隔离区-----------隔离区-----------隔离区-----------------------------------------------
		
		//根据id查询数据库整条user
		public User querById(int id) {
			String sql="select * from users where id=?";
			Connection connection=DBConnection.getConnection();
			User user=null;
			try {
				PreparedStatement psmt=connection.prepareStatement(sql);
				psmt.setInt(1, id);
				ResultSet rs=psmt.executeQuery();
				if(rs.next()) {
					user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				}
			} catch (SQLException e) {
				System.out.println(UserTableOperation.class.getName()+": query By Id method error,message is "+e.getMessage());
			}
			return user;
		}
		
		//根据name进行查询  用JdbcTemplate接口
		public User querByNameX(String name) {
			String sql="select * from users where name=?";
			User user=JdbcTemplate.query(sql, (rs)->{
				User userx=null;
				try {
					while(rs.next()) {
						userx=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return userx;
			}, name);
			
			return user;
		}
		
		//根据name查询数据库整条user
		public User querByName(String name) {
			User user=null;
			String sql="select * from users where name=?";
			Connection conn=DBConnection.getConnection();
			try {
				PreparedStatement psmt=conn.prepareStatement(sql);
				psmt.setString(1, name);
				ResultSet rs=psmt.executeQuery();
				if(rs.next()) {
					user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				}
			} catch (SQLException e) {
				System.out.println(UserTableOperation.class.getName()+": query By Name method error,message is "+e.getMessage());
			}
			return user;
		}
		//根据name 模糊查询数据库内容并返回List<User>
		public List<User> queryAllM(String name){
			String sql="select * from users where name like ?";
			List<User> list=JdbcTemplate.query(sql, (rs)->{
				List<User> list0=new ArrayList<>();
				try {
					while(rs.next()) {
						User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
						list0.add(user);
					}
				}catch(SQLException e) {
					e.printStackTrace();
				}
				return list0;
			}, "%"+name+"%");
			return list;
		}
				
		//数据库中添加一条新的user  用JdbcTemplate接口
		public int addUser(User user) {
			
			String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
			return JdbcTemplate.update(sql, user);
			
		}
		
		//根据id的删除操作  用JdbcTemplate接口
				public int deleteUserX(int id) {
					String sql="delete from users where id=?";
					int rowNum=JdbcTemplate.update(sql, id);
					return rowNum;
				}
				
				
				
		//根据id的删除操作
		public int deleteUser(int id) {
			int rowNum=0;
			String sql="delete from users where id=?";
			Connection conn=DBConnection.getConnection();
			try {
				PreparedStatement psmt=conn.prepareStatement(sql);
				psmt.setInt(1, id);
				
				rowNum=psmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println(UserTableOperation.class.getName()+": delete users by id method error,message is "+e.getMessage());
			}
			
			return rowNum;
		}
		
		//根据name的删除操作  用JdbcTemplate接口
				public int deleteUserX(String name) {
					String sql="delete from users where name=?";
					int rowNum=JdbcTemplate.update(sql,name);
					return rowNum;
						
				}
		
		//根据name的删除操作
		public int deleteUser(String name) {
			int rowNum=0;
			String sql="delete from users where name=?";
			Connection conn=DBConnection.getConnection();
			try {
				PreparedStatement psmt=conn.prepareStatement(sql);
				psmt.setString(1, name);
				
				rowNum=psmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println(UserTableOperation.class.getName()+": delete users by name method error,message is "+e.getMessage());
			}
			
			return rowNum;
				
		}
		//修改操作 传入sql语句和一个不定长参数
		public int updateUser(String sql,Object...param) {
			
			return JdbcTemplate.update(sql, param);
		}
		
}
